package com.open.exam.sqlprovider;

import com.open.exam.enums.ParameterKey;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.StringUtils;

import java.util.HashMap;
import java.util.Map;

/**
 * @author 奶茶
 * Created on 2017/11/14.
 */
public class ExamineeArrangeProvider {

    private static Logger logger = LoggerFactory.getLogger(ExamineeArrangeProvider.class);

    public String findExamCountList(){
        StringBuilder stringBuffer = new StringBuilder();
        stringBuffer.append("SELECT\n" +
                "\tdatagenerated.ExamPlanInfoID AS examPlanInfoId,\n" +
                "\tCOUNT(*) AS count\n" +
                "FROM\n" +
                "\t(\n" +
                "\t\tSELECT\n" +
                "\t\t\tdataArranges.ExamPlanInfoID,\n" +
                "\t\t\tdataArranges.ExamSubjectID,\n" +
                "\t\t\tCOUNT(*) AS count\n" +
                "\t\tFROM\n" +
                "\t\t\tExamineeArrange AS dataArranges\n" +
                "\t\tJOIN ExamPlanInfo AS dataPlan\n" +
                "\t\tWHERE\n" +
                "\t\t\tdataArranges.ExamPlanInfoID = dataPlan.ExamPlanInfoID\n" +
                "\t\tGROUP BY\n" +
                "\t\t\tdataArranges.ExamPlanInfoID,\n" +
                "\t\t\tdataArranges.ExamSubjectID\n" +
                "\t) AS dataarrange\n" +
                "JOIN (\n" +
                "\tSELECT\n" +
                "\t\tpaper.ExamPlanInfoID,\n" +
                "\t\tpaper.ExamSubjectID,\n" +
                "\t\tCOUNT(*) AS count\n" +
                "\tFROM\n" +
                "\t\tExamineeExamPaper AS paper\n" +
                "\tGROUP BY\n" +
                "\t\tpaper.ExamPlanInfoID,\n" +
                "\t\tpaper.ExamSubjectID\n" +
                ") AS datagenerated\n" +
                "WHERE\n" +
                "\tdataarrange.ExamPlanInfoID = datagenerated.ExamPlanInfoID \n" +
                "AND dataarrange.ExamSubjectID = datagenerated.ExamSubjectID\n" +
                "AND dataarrange.count = datagenerated.count\n" +
                "GROUP BY\n" +
                "\tdatagenerated.ExamPlanInfoID");
        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public static String findExamPlanViewModelBySomeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String siteId = (String) parameter.get("siteId");
        String examPlanName = (String) parameter.get("examPlanName");
        Short planType = Short.valueOf(parameter.get("planType").toString());
        stringBuffer.append("SELECT dataplan.AppID,dataplan.ExamPlanInfoID batchId,dataplan.ExamPlanName,dataplan.ExamMode,datasubcount.count as ExamSubjectCount,dataplan.BeginTime,dataplan.EndTime,dataplan.SysState,dataplan.ExamPlanType,dataplan.SeatByNumberRequired FROM ExamPlanInfo AS dataplan");
        stringBuffer.append(" JOIN ( SELECT dataSub.ExamPlanInfoID,COUNT(*) AS count FROM ExamSubject AS dataSub GROUP BY dataSub.ExamPlanInfoID,dataSub.AppID ) AS datasubcount ON dataplan.ExamPlanInfoID = datasubcount.ExamPlanInfoID");
        stringBuffer.append(" WHERE dataplan.ExamSiteID = \'").append(siteId).append("\' ").append(" AND dataplan.ExamPlanType = ").append(planType).append(" AND dataplan.AppID = \'").append(appId).append("\'");
        if(!StringUtils.isEmpty(examPlanName)){
            stringBuffer.append(" AND dataplan.ExamPlanName LIKE \'%").append(examPlanName).append("%\'");
        }
        stringBuffer.append(" ORDER BY dataplan.BeginTime");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String getGeneratedCount(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String appId = (String) parameter.get("appId");

        stringBuffer.append("SELECT\n" +
                "\tcount(1)\n" +
                "FROM\n" +
                "ExamSubject AS y\n" +
                "LEFT JOIN ExamineeExamPaper AS x ON x.ExamPlanInfoID = y.ExamPlanInfoID\n" +
                "WHERE");

        stringBuffer.append(" x.ExamPlanInfoID = \'").append(examPlanInfoID).append("\'");
        stringBuffer.append(" AND x.ExamSubjectID = y.ExamSubjectID AND x.AppID = y.AppID AND x.AppID = \'").append(appId).append("\'");


        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String findArranges(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String planId = (String) parameter.get("planId");
        String appId = (String) parameter.get("appId");

        stringBuffer.append("SELECT arrange.AppID,arrange.ExamPlanInfoID,arrange.ExamSubjectID,arrange.ExamSceneID,COUNT(*) AS count FROM ExamineeArrange AS arrange JOIN ( ");
        stringBuffer.append("SELECT * FROM ExamPlanInfo WHERE ExamPlanInfoID = \'").append(planId).append("\' ").append(" AND AppID = \'").append(appId).append("\' ) AS data1 ");
        stringBuffer.append("ON arrange.ExamPlanInfoID = data1.ExamPlanInfoID WHERE arrange.AppID = data1.AppID GROUP BY arrange.AppID, arrange.ExamPlanInfoID, arrange.ExamSubjectID, arrange.ExamSceneID");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String findExamComputerBySomeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String examPlanInfoId = (String) parameter.get("examPlanInfoId");

        stringBuffer.append("SELECT x.* FROM ExamComputer AS x JOIN ");
        stringBuffer.append("( SELECT * FROM ExamineeArrange WHERE AppID = \'").append(appId).append("\' AND ExamPlanInfoID = \'").append(examPlanInfoId).append("\' ) AS y ");
        stringBuffer.append("ON x.SeatNo = y.SeatNo");

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examineeArrangeWhere(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String appId = (String) parameter.get("appId");
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examSubjectID = (String) parameter.get("examSubjectID");

        stringBuffer.append("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = \'").append(examPlanInfoID).append("\' AND ExamSubjectID = \'").append(examSubjectID).append("\' AND AppID = \'").append(appId).append("\'");
        stringBuffer.append(" AND ExamPaperID = ''");
        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public String examineeArrangeMoniter(Map<String,Object> parameter){
        StringBuilder stringBuffer = new StringBuilder();
        String examPlanInfoID = (String) parameter.get("examPlanInfoID");
        String examRoomID = (String) parameter.get("examRoomID");

        stringBuffer.append("SELECT * FROM ExamineeArrange WHERE ExamPlanInfoID = \'").append(examPlanInfoID).append("\' ").append(" AND ExamRoomID = \'").append(examRoomID).append("\' ");
        if(parameter.get("examineeID") != null){
            stringBuffer.append(" AND ExamineeID = \'").append((String) parameter.get("examineeID")).append("\' ");
        }
        if(parameter.get("appID") != null){
            stringBuffer.append(" AND AppID = \'").append((String)parameter.get("appID")).append("\'");
        }
        if(parameter.get("examSceneID") != null){
            stringBuffer.append(" AND ExamSceneID = \'").append((String)parameter.get("examSceneID")).append("\' ");
        }
        if(parameter.get("examSubjectID") != null){
            stringBuffer.append(" AND ExamSubjectID = \'").append((String)parameter.get("examSubjectID")).append("\' ");
        }

        String sql = stringBuffer.toString();
        logger.info(sql);
        return sql;
    }

    public static void main(String[] args){
        Map<String,Object> map = new HashMap<>();
        map.put("appID","1");
        map.put("examPlanInfoID",66);
        map.put("examRoomID",21);
        map.put("examSubjectID",246);
//        System.out.println(examineeArrangeMoniter(map));
    }
}
